package com.statistics.config;

import com.statistics.entity.StatisticsDto;
import com.statistics.entity.SupplementDto;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * @author hewei
 * @date 2022/4/25 15:24
 * @Description
 */
public class ExportExcelUtil {
    public static void ExportExcel(Map<String, List<StatisticsDto>> map, String[] titles) {
        int size = 0;
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet();
        Iterator<String> iterator = map.keySet().iterator();
        while (iterator.hasNext()){
            String mapKey = iterator.next();
            List<StatisticsDto> list = map.get(mapKey);
            CellStyle cellStyle1 = excel(size,sheet,wb,titles);
            for (int j = 0; j < list.size(); j++) {
                Row rowData = sheet.createRow(size+j+1);
                StatisticsDto person = list.get(j);
                Cell operator = rowData.createCell(0);
                operator.setCellValue(person.getOperator());
                operator.setCellStyle(cellStyle1);
                Cell name = rowData.createCell(1);
                name.setCellValue(person.getName());
                name.setCellStyle(cellStyle1);
                Cell idCard = rowData.createCell(2);
                idCard.setCellValue(person.getIdCard());
                idCard.setCellStyle(cellStyle1);
                Cell account = rowData.createCell(3);
                account.setCellValue(person.getAccount());
                account.setCellStyle(cellStyle1);
                Cell operatorDate = rowData.createCell(4);
                operatorDate.setCellStyle(cellStyle1);
                operatorDate.setCellValue(person.getOperatorDate());
                Cell resultMode = rowData.createCell(5);
                resultMode.setCellStyle(cellStyle1);
                resultMode.setCellValue(person.getResultMode());
                Cell noticeObj = rowData.createCell(6);
                noticeObj.setCellValue(person.getNoticeObj());
                noticeObj.setCellStyle(cellStyle1);
                Cell phone = rowData.createCell(7);
                phone.setCellStyle(cellStyle1);
                phone.setCellValue(person.getPhone());
                Cell addressType = rowData.createCell(8);
                addressType.setCellStyle(cellStyle1);
                addressType.setCellValue(person.getAddressType());
                Cell address = rowData.createCell(9);
                address.setCellStyle(cellStyle1);
                address.setCellValue(person.getAddress());
                Cell liaisonResult = rowData.createCell(10);
                liaisonResult.setCellStyle(cellStyle1);
                if (null != person.getLiaisonResult()){
                    liaisonResult.setCellValue(Integer.parseInt(person.getLiaisonResult()));
                }else {
                    liaisonResult.setCellValue("");
                }

                Cell result = rowData.createCell(11);
                result.setCellStyle(cellStyle1);
                if (null != person.getResult()){

                    result.setCellValue(Integer.parseInt(person.getResult()));
                }else {
                    result.setCellValue("");
                }
                Cell sign = rowData.createCell(12);
                sign.setCellStyle(cellStyle1);
                sign.setCellValue(person.getSign());
                Cell record = rowData.createCell(13);
                record.setCellStyle(cellStyle1);
                record.setCellValue(person.getRecord());
            }
            size = size+list.size()+1;
        }
        String fileName = "E:/人员信息导出.xlsx";
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            wb.write(fileOutputStream);
            wb.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void ExportExcels(Map<String, List<SupplementDto>> map, String[] titles) {
        int size = 0;
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet();
        Iterator<String> iterator = map.keySet().iterator();
        CellStyle cellStyle1 = excel(size,sheet,wb,titles);
        while (iterator.hasNext()){
            String mapKey = iterator.next();
            List<SupplementDto> list = map.get(mapKey);
            for (int j = 0; j < list.size(); j++) {
                Row rowData = sheet.createRow(size+j+1);
                SupplementDto person = list.get(j);
                Cell name = rowData.createCell(0);
                name.setCellValue(person.getName());
                name.setCellStyle(cellStyle1);

                Cell cardNumber = rowData.createCell(1);
                cardNumber.setCellValue(person.getCardNumber());
                cardNumber.setCellStyle(cellStyle1);

                Cell idCard = rowData.createCell(2);
                idCard.setCellValue(person.getIdCard());
                idCard.setCellStyle(cellStyle1);

                Cell relationship = rowData.createCell(3);
                relationship.setCellValue(person.getRelationship());
                relationship.setCellStyle(cellStyle1);

                Cell phone = rowData.createCell(4);
                phone.setCellStyle(cellStyle1);
                phone.setCellValue(person.getPhone());

                Cell liaisonResult = rowData.createCell(5);
                liaisonResult.setCellStyle(cellStyle1);
                liaisonResult.setCellValue(person.getLiaisonResult());

                Cell result = rowData.createCell(6);
                result.setCellStyle(cellStyle1);
                result.setCellValue(person.getResult());


                Cell resultDate = rowData.createCell(7);
                resultDate.setCellValue(person.getResultDate());
                resultDate.setCellStyle(cellStyle1);


                Cell resultMode = rowData.createCell(8);
                resultMode.setCellStyle(cellStyle1);
                resultMode.setCellValue(person.getResultMode());


                Cell resultContent = rowData.createCell(9);
                resultContent.setCellStyle(cellStyle1);
                resultContent.setCellValue(person.getResultContent());

                Cell resultStatus = rowData.createCell(10);
                resultStatus.setCellStyle(cellStyle1);
                resultStatus.setCellValue(person.getResultStatus());
            }
            size = size+list.size();
        }
        String fileName = "D:/系统文件.xlsx";
        try {
            FileOutputStream fileOutputStream = new FileOutputStream(fileName);
            wb.write(fileOutputStream);
            wb.close();
            fileOutputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static CellStyle excel(int size,Sheet sheet,SXSSFWorkbook wb, String[] titles){

        Row row = sheet.createRow(size);
        CellRangeAddress cellAddresses = CellRangeAddress.valueOf("A:N");
        sheet.setAutoFilter(cellAddresses);
        //给单元格设置样式
        CellStyle cellStyle = wb.createCellStyle();
        Font font = wb.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 11);
        //设置字体加粗
        font.setBold(true);
        font.setColor(IndexedColors.WHITE.getIndex());
        font.setFontName("宋体");
        //给字体设置样式
        cellStyle.setFont(font);

        //设置单元格背景颜色
        cellStyle.setFillForegroundColor((short)23);
        //设置单元格填充样式(使用纯色背景颜色填充)
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(cellStyle);
            //设置列的宽度
            switch (i){
                case 0:
                case 1:
                case 5:
                case 6:
                case 10:
                case 11:
                    sheet.setColumnWidth(i, 80*60);
                    break;
                default:
                    sheet.setColumnWidth(i, 120*50);
            }

        }
        CellStyle cellStyle1 = wb.createCellStyle();
        Font font1 = wb.createFont();
        //设置字体大小
        font1.setFontHeightInPoints((short) 9);
        //设置字体加粗
        font1.setFontName("宋体");
        font1.setColor(IndexedColors.BLACK.getIndex());
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        //给字体设置样式
        cellStyle1.setFont(font1);
        return cellStyle1;
    }


}
